package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import sun.jdbc.odbc.*;
import sun.jdbc.*;

public class MDBDocument {
	private Connection con;
	ResultSet rs;
	Statement s;
	Statement us;
	private static int id=0;
	public MDBDocument(String path) throws  SQLException, ClassNotFoundException
	{
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

		String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
		database+= path.trim() + ";DriverID=22;READONLY=true}";
		con = DriverManager.getConnection( database ,"",""); 

		
		s = con.createStatement();
		us=con.createStatement();
		us.executeUpdate("DELETE FROM Geo_Value_Year_Month");
		
	}
	
	public void reopen() throws SQLException
	{
		s.execute("select * from GEO_DE_distinct");
		rs = s.getResultSet(); 
	}
	public void close()
	{
		if(rs!=null)
			try {
				rs.close();
				con.close();
			} catch (SQLException e) {
			}
		
		
	}
	public double[] next() throws SQLException
	{
		if (rs== null)
			return null;
		if(!rs.next())
			return null;
		double[] coor= new double[2];
		coor[0]=rs.getDouble("Longitude");
		coor[1]=rs.getDouble("Lattitude");
		return coor;
	}
	
	public void update(String key, Integer value) throws SQLException
	{
		int ID= rs.getInt("ID");
		int year;
		int month;
		String [] keys= key.split("_");
		try{
			year=Integer.parseInt(keys[0]);
			if(keys.length>=2)
				month=Integer.parseInt(keys[1]);
			else
				month=0;
		}
		catch(NumberFormatException e)
		{
			keys= keys[0].split("-");
			year= Integer.valueOf(keys[1]);
			month= -((year-Integer.valueOf(keys[0])+1)*12);
		}
		us.executeUpdate("insert into Geo_Value_Year_Month VALUES ("+id+","+ID+","+year +","+month+","+value+")");
		id++;
		/*
		s.execute("select * from GEO_Value where GEO_ID='"+ID+"'");

		if(s.getResultSet().)
		{
			}
		else
		{
			s.executeUpdate("update GEO_Value set "+ key +"= "+value+" where ID="+ID);
			}
		s.close();
		*/
	}
}
